package com.wj.facedetect.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.text.TextUtils;
import android.util.Log;

import com.wj.facedetect.util.PreferencesUtils;
import com.wj.facedetect.util.TimeUtils;

import java.util.ArrayList;
import java.util.List;

public class ChatMsgDao {
    private DBHelper helper;

    public ChatMsgDao(Context context) {
        helper = new DBHelper(context);
    }

    public ChatMsgDao(Context context, int version) {
        helper = new DBHelper(context, version);
    }

    /**
     * 添加新信息
     *
     * @param msg
     */
    public int insertMsg(Msg msg) {
        if (msg == null) {
            return -1;
        }
        if (isExistMsg(msg.getMsgId() + "")) {
            return -1;
        }
        SQLiteDatabase db = helper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(DBcolumns.MSG_ID_S, msg.getMsgId());
        values.put(DBcolumns.MSG_FROM, msg.getFromUser());
        values.put(DBcolumns.MSG_TO, msg.getToUser());
        values.put(DBcolumns.MSG_TYPE, msg.getType());
        values.put(DBcolumns.MSG_CONTENT, msg.getContent());
        values.put(DBcolumns.MSG_ISCOMING, msg.getIsComing());
        values.put(DBcolumns.MSG_DATE, msg.getDate());
        values.put(DBcolumns.MSG_ISREADED, msg.getIsReaded());
        values.put(DBcolumns.MSG_GROUP_ID, msg.getGroupId());
        values.put(DBcolumns.MSG_BAK1, msg.getBak1());
        values.put(DBcolumns.MSG_BAK2, msg.getBak2());
        values.put(DBcolumns.MSG_BAK3, msg.getBak3());
        values.put(DBcolumns.MSG_BAK4, msg.getBak4());
        values.put(DBcolumns.MSG_BAK5, msg.getBak5());
        values.put(DBcolumns.MSG_BAK6, msg.getBak6());
        db.insert(DBcolumns.TABLE_MSG, null, values);
        int id = queryTheLastMsgId();
        db.close();
        return id;
    }


    /**
     * 清空所有聊天记录
     */
    public void deleteTableData() {
        SQLiteDatabase db = helper.getWritableDatabase();
        db.delete(DBcolumns.TABLE_MSG, null, null);
        db.close();
    }


    /**
     * 根据msgid，删除对应聊天记录
     *
     * @return
     */
    public long deleteMsgById(int msgid) {
        SQLiteDatabase db = helper.getWritableDatabase();
        long row = db.delete(DBcolumns.TABLE_MSG, DBcolumns.MSG_ID + " = ?", new String[]{"" + msgid});
        db.close();
        return row;
    }

    /**
     * 查询列表,每页返回15条,依据id逆序查询，将时间最早的记录添加进list的最前面
     *
     * @return
     */
    public ArrayList<Msg> queryMsg(String groupId, int offset) {
        ArrayList<Msg> list = new ArrayList<Msg>();
        SQLiteDatabase db = helper.getWritableDatabase();
//        String sql = "select * from " + DBcolumns.TABLE_MSG + " where " + DBcolumns.MSG_FROM + "=? and " + DBcolumns.MSG_TO + "=? order by " + DBcolumns.MSG_ID + " desc limit ?,?";
//        String[] args = new String[]{from, to, String.valueOf(offset), "15"};
        String sql = "select * from " + DBcolumns.TABLE_MSG + " where " + DBcolumns.MSG_GROUP_ID + "=? order by " + DBcolumns.MSG_ID_S + " desc limit ?,?";
        String[] args = new String[]{groupId, String.valueOf(offset), "20"};
        Cursor cursor = db.rawQuery(sql, args);
        Msg msg = null;
        while (cursor.moveToNext()) {
            msg = new Msg();
            msg.setMsgId(cursor.getInt(cursor.getColumnIndex(DBcolumns.MSG_ID_S)));
            msg.setFromUser(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_FROM)));
            msg.setToUser(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_TO)));
            msg.setType(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_TYPE)));
            msg.setContent(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_CONTENT)));
            msg.setIsComing(cursor.getInt(cursor.getColumnIndex(DBcolumns.MSG_ISCOMING)));
            msg.setDate(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_DATE)));
            msg.setIsReaded(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_ISREADED)));
            msg.setBak1(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_BAK1)));
            msg.setBak2(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_BAK2)));
            msg.setBak3(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_BAK3)));
            msg.setBak4(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_BAK4)));
            msg.setBak5(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_BAK5)));
            msg.setBak6(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_BAK6)));
            list.add(0, msg);
        }
        cursor.close();
        db.close();
        return list;
    }

    /**
     * 查询最新一条记录
     *
     * @return
     */
    public Msg queryTheLastMsg() {
        SQLiteDatabase db = helper.getWritableDatabase();
        String sql = "select * from " + DBcolumns.TABLE_MSG + " order by " + DBcolumns.MSG_ID_S + " desc limit 1";
        String[] args = new String[]{};
        Cursor cursor = db.rawQuery(sql, args);

        Msg msg = null;
        while (cursor.moveToNext()) {
            msg = new Msg();
            msg.setMsgId(cursor.getInt(cursor.getColumnIndex(DBcolumns.MSG_ID)));
            msg.setFromUser(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_FROM)));
            msg.setToUser(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_TO)));
            msg.setType(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_TYPE)));
            msg.setContent(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_CONTENT)));
            msg.setIsComing(cursor.getInt(cursor.getColumnIndex(DBcolumns.MSG_ISCOMING)));
            msg.setDate(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_DATE)));
            msg.setIsReaded(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_ISREADED)));
            msg.setBak1(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_BAK1)));
            msg.setBak2(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_BAK2)));
            msg.setBak3(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_BAK3)));
            msg.setBak4(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_BAK4)));
            msg.setBak5(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_BAK5)));
            msg.setBak6(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_BAK6)));
        }
        cursor.close();
        db.close();
        return msg;
    }

    /**
     * 查询最新一条记录
     *
     * @return
     */
    public Msg queryTheLastMsgByGroupId(String groupId) {
        SQLiteDatabase db = helper.getWritableDatabase();
        String sql = "select * from " + DBcolumns.TABLE_MSG + " where " + DBcolumns.MSG_GROUP_ID + "=?" + " order by " + DBcolumns.MSG_ID_S + " desc limit 1";
        String[] args = new String[]{groupId};
        Cursor cursor = db.rawQuery(sql, args);

        Msg msg = null;
        while (cursor.moveToNext()) {
            msg = new Msg();
            msg.setMsgId(cursor.getInt(cursor.getColumnIndex(DBcolumns.MSG_ID)));
            msg.setFromUser(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_FROM)));
            msg.setToUser(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_TO)));
            msg.setType(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_TYPE)));
            msg.setContent(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_CONTENT)));
            msg.setIsComing(cursor.getInt(cursor.getColumnIndex(DBcolumns.MSG_ISCOMING)));
            msg.setDate(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_DATE)));
            msg.setIsReaded(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_ISREADED)));
            msg.setBak1(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_BAK1)));
            msg.setBak2(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_BAK2)));
            msg.setBak3(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_BAK3)));
            msg.setBak4(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_BAK4)));
            msg.setBak5(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_BAK5)));
            msg.setBak6(cursor.getString(cursor.getColumnIndex(DBcolumns.MSG_BAK6)));
        }
        cursor.close();
        db.close();
        return msg;
    }

    /**
     * 查询最新一条记录的id
     *
     * @return
     */
    public int queryTheLastMsgId() {
        SQLiteDatabase db = helper.getWritableDatabase();
        String sql = "select " + DBcolumns.MSG_ID + " from " + DBcolumns.TABLE_MSG + " order by " + DBcolumns.MSG_ID_S + " desc limit 1";
        String[] args = new String[]{};
        Cursor cursor = db.rawQuery(sql, args);
        int id = -1;
        if (cursor.moveToNext()) {
            id = cursor.getInt(cursor.getColumnIndex(DBcolumns.MSG_ID));
        }
        cursor.close();
        db.close();
        return id;
    }

    /**
     * 查询所有信息未读数量
     */
    public int queryAllNotReadCount() {
        SQLiteDatabase db = helper.getWritableDatabase();
        int count = 0;
        Cursor cursor = db.rawQuery("select count(*) from " + DBcolumns.TABLE_MSG + " where " + DBcolumns.MSG_ISREADED + " = 0", null);
        cursor.moveToFirst();
        count = cursor.getInt(0);
        cursor.close();
        return count;
    }

    /**
     * 查询所有信息未读数量by groupId
     */
    public int queryAllNotReadCountByGroupId(String groupId) {
        SQLiteDatabase db = helper.getWritableDatabase();
        int count = 0;
        Cursor cursor = db.rawQuery("select count(*) from " + DBcolumns.TABLE_MSG + " where " + DBcolumns.MSG_ISREADED + " =?" + " and " + DBcolumns.MSG_GROUP_ID + "=?", new String[]{"0", groupId});
        cursor.moveToFirst();
        count = cursor.getInt(0);
        cursor.close();
        return count;
    }

    public int queryAllNotReadCount(String to) {
        SQLiteDatabase db = helper.getWritableDatabase();
        int count = 0;
        Cursor cursor = db.rawQuery("select count(*) from " + DBcolumns.TABLE_MSG + " where " + DBcolumns.MSG_ISREADED + " =?" + " and " + DBcolumns.MSG_TO + " =?", new String[]{"0", to});
        cursor.moveToFirst();
        count = cursor.getInt(0);
        cursor.close();
        return count;
    }

    /**
     * 更新所有信息为已读
     */
    public long updateAllMsgToRead(String from, String to) {
        SQLiteDatabase db = helper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(DBcolumns.MSG_ISREADED, "1");
        long row = db.update(DBcolumns.TABLE_MSG, values, DBcolumns.MSG_FROM + " =? and " + DBcolumns.MSG_TO + " =? and " + DBcolumns.MSG_ISREADED + "=0 ", new String[]{from, to});
        db.close();
        return row;
    }

    /**
     * 更新所有信息为已读
     */
    public long updateAllMsgToRead(String groupId) {
        SQLiteDatabase db = helper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(DBcolumns.MSG_ISREADED, "1");
        long row = db.update(DBcolumns.TABLE_MSG, values, DBcolumns.MSG_GROUP_ID + " =? and " + DBcolumns.MSG_ISREADED + "=0 ", new String[]{groupId});
        db.close();
        return row;
    }

    public long deleteAllMsg(String from, String to) {
        SQLiteDatabase db = helper.getWritableDatabase();
        long row = db.delete(DBcolumns.TABLE_MSG, DBcolumns.MSG_FROM + " = ?" + " AND " + DBcolumns.MSG_TO + " = ?", new String[]{from, to});
        db.close();
        return row;
    }

    /**
     * @param groupId   插入group的信息
     * @param longitude
     * @param latitude
     */
    public void insertGroupInfo(String groupId, String longitude, String latitude) {
        SQLiteDatabase db = helper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(DBcolumns.GROUP_NUM, groupId);
        values.put(DBcolumns.GROUP_LONGITUDE, longitude);
        values.put(DBcolumns.GROUP_LATITUDE, latitude);
        //values.put(DBcolumns.GROUP_TIME, Utils.timeFormat());
        values.put(DBcolumns.GROUP_TIME, System.currentTimeMillis());
        values.put(DBcolumns.GROUP_FINISH, 0);//group是否结束的标识:0代表没有结束，1代表结束
        values.put(DBcolumns.GROUP_BAK1, "");
        values.put(DBcolumns.GROUP_BAK2, "");
        long id = db.insert(DBcolumns.GROUP_TABLE, null, values);
        if (id == -1) {
            Log.e("tag", "insertGroupInfo failed return -1");
        }
        db.close();
    }

    /**
     * @param groupInfo 插入group的信息
     */
    public void insertGroupInfo(GroupInfo groupInfo) {
        if (groupInfo == null) {
            return;
        }
        if (isExist(groupInfo.getGroupId())) {
            return;
        }
        SQLiteDatabase db = helper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(DBcolumns.GROUP_NUM, groupInfo.getGroupId());
        values.put(DBcolumns.GROUP_LONGITUDE, String.valueOf(groupInfo.getLongitude()));
        values.put(DBcolumns.GROUP_LATITUDE, String.valueOf(groupInfo.getLatitude()));
        values.put(DBcolumns.GROUP_TIME, System.currentTimeMillis());
        values.put(DBcolumns.GROUP_FINISH, 0);//group是否结束的标识:0代表没有结束，1代表结束
        //查询该群的未读信息条数
        values.put(DBcolumns.GROUP_UNREADCOUNT, groupInfo.getUnReadCount());
        //查询该群的最后一条消息
        values.put(DBcolumns.GROUP_BAK2, groupInfo.getBak2());
        //userId
        values.put(DBcolumns.GROUP_BAK1, PreferencesUtils.getInstance().getSharePreInt(PreferencesUtils.USER_ID) + "");
        values.put(DBcolumns.GROUP_NAME, groupInfo.getGroupName());
        long id = db.insert(DBcolumns.GROUP_TABLE, null, values);
        if (id == -1) {
            Log.e("tag", "insertGroupInfo failed return -1");
        } else {
            Log.e("tag", "insertGroupInfo failed return " + id);
        }
        db.close();
    }

    /**
     * @param groupId 更新组的状态
     */
    public void updateGroupStatus(String groupId) {
        SQLiteDatabase db = helper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(DBcolumns.GROUP_FINISH, 1);
        db.update(DBcolumns.GROUP_TABLE, values, DBcolumns.GROUP_NUM + "=?", new String[]{groupId});
    }

    /**
     * @param groupId 更新组的状态
     */
    public void updateGroupTime(String groupId) {
        SQLiteDatabase db = helper.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(DBcolumns.GROUP_TIME, System.currentTimeMillis());
        db.update(DBcolumns.GROUP_TABLE, values, DBcolumns.GROUP_NUM + "=?", new String[]{groupId});
    }

    /**
     * @return 查询所有组的信息
     */
    public List<GroupInfo> queryGroupInfos() {
        List<GroupInfo> result = new ArrayList<>();
        String sql = "select * from " + DBcolumns.GROUP_TABLE + " where " + DBcolumns.GROUP_BAK1 + "=? order by " + DBcolumns.GROUP_ID + " desc";
        SQLiteDatabase db = helper.getWritableDatabase();
        String loginId = PreferencesUtils.getInstance().getSharePreInt(PreferencesUtils.USER_ID) + "";
        Cursor cursor = db.rawQuery(sql, new String[]{loginId});
        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex(DBcolumns.GROUP_ID));
            int groupStatus = cursor.getInt(cursor.getColumnIndex(DBcolumns.GROUP_FINISH));
            String groupNum = cursor.getString(cursor.getColumnIndex(DBcolumns.GROUP_NUM));
            String groupLong = cursor.getString(cursor.getColumnIndex(DBcolumns.GROUP_LONGITUDE));
            String groupLati = cursor.getString(cursor.getColumnIndex(DBcolumns.GROUP_LATITUDE));
            long groupTime = cursor.getLong(cursor.getColumnIndex(DBcolumns.GROUP_TIME));
            String groupBak1 = cursor.getString(cursor.getColumnIndex(DBcolumns.GROUP_BAK1));
            String groupBak2 = cursor.getString(cursor.getColumnIndex(DBcolumns.GROUP_BAK2));
            String groupName = cursor.getString(cursor.getColumnIndex(DBcolumns.GROUP_NAME));
            GroupInfo info = new GroupInfo();
            int unReadCount = queryAllNotReadCountByGroupId(groupNum);
            Msg msg = queryTheLastMsgByGroupId(groupNum);
            info.setId(id);
            info.setGroupId(groupNum);
            info.setLongitude(Double.parseDouble(groupLong));
            info.setLatitude(Double.parseDouble(groupLati));
            info.setBak1(groupBak1);
            info.setIsOver(groupStatus);
            info.setGroupName(groupName);
            info.setUnReadCount(unReadCount);
            if (msg != null) {
                info.setTime(TimeUtils.getTimestamp(msg.getDate(), TimeUtils.DATE_FORMAT_YYYYMMDDHHMMSS));
                info.setBak2(msg.getContent());
            } else {
                info.setTime(groupTime);
            }
            result.add(info);
        }
        cursor.close();
        db.close();
        return result;
    }

    /**
     * @param nowTime 按时间查询
     * @return 查询所有组的信息
     */
    public List<GroupInfo> queryGroupInfosByTime(long nowTime) {
        List<GroupInfo> result = new ArrayList<>();
        //String sql = "select * from " + DBcolumns.GROUP_TABLE + " where " + DBcolumns.GROUP_BAK2 + "=? order by " + DBcolumns.GROUP_ID + " desc" ;
        String sql = "select * from " + DBcolumns.GROUP_TABLE + " where " + DBcolumns.GROUP_BAK2 + "=? and " + DBcolumns.GROUP_TIME + ">? order by " + DBcolumns.GROUP_ID + " desc";
        SQLiteDatabase db = helper.getWritableDatabase();
        String loginId = PreferencesUtils.getInstance().getSharePreStr(PreferencesUtils.USER_ID);
        Cursor cursor = db.rawQuery(sql, new String[]{loginId, nowTime + ""});
        while (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex(DBcolumns.GROUP_ID));
            int groupStatus = cursor.getInt(cursor.getColumnIndex(DBcolumns.GROUP_FINISH));
            String groupNum = cursor.getString(cursor.getColumnIndex(DBcolumns.GROUP_NUM));
            String groupLong = cursor.getString(cursor.getColumnIndex(DBcolumns.GROUP_LONGITUDE));
            String groupLati = cursor.getString(cursor.getColumnIndex(DBcolumns.GROUP_LATITUDE));
            long groupTime = cursor.getLong(cursor.getColumnIndex(DBcolumns.GROUP_TIME));
            String groupBak1 = cursor.getString(cursor.getColumnIndex(DBcolumns.GROUP_BAK1));
            String groupBak2 = cursor.getString(cursor.getColumnIndex(DBcolumns.GROUP_BAK2));
            GroupInfo info = new GroupInfo();
            info.setId(id);
            info.setGroupId(groupNum);
            info.setLongitude(Double.parseDouble(groupLong));
            info.setLatitude(Double.parseDouble(groupLati));
            info.setTime(groupTime);
            info.setBak1(groupBak1);
            info.setBak2(groupBak2);
            info.setIsOver(groupStatus);
            result.add(info);
        }
        cursor.close();
        db.close();
        return result;
    }

    /**
     * @param groupId 组的id
     * @return
     */
    public GroupInfo queryGroupInfo(String groupId) {
        SQLiteDatabase db = helper.getWritableDatabase();
        String sql = "select * from " + DBcolumns.GROUP_TABLE + " where " + DBcolumns.GROUP_ID + " =?";
        Cursor cursor = db.rawQuery(sql, new String[]{groupId});
        GroupInfo info = new GroupInfo();
        if (cursor.moveToNext()) {
            int id = cursor.getInt(cursor.getColumnIndex(DBcolumns.GROUP_ID));
            int groupStatus = cursor.getInt(cursor.getColumnIndex(DBcolumns.GROUP_FINISH));
            String groupNum = cursor.getString(cursor.getColumnIndex(DBcolumns.GROUP_NUM));
            String groupLong = cursor.getString(cursor.getColumnIndex(DBcolumns.GROUP_LONGITUDE));
            String groupLati = cursor.getString(cursor.getColumnIndex(DBcolumns.GROUP_LATITUDE));
            long groupTime = cursor.getLong(cursor.getColumnIndex(DBcolumns.GROUP_TIME));
            String groupBak1 = cursor.getString(cursor.getColumnIndex(DBcolumns.GROUP_BAK1));
            String groupBak2 = cursor.getString(cursor.getColumnIndex(DBcolumns.GROUP_BAK2));
            info.setId(id);
            info.setGroupId(groupNum);
            info.setLongitude(Double.parseDouble(groupLong));
            info.setLatitude(Double.parseDouble(groupLati));
            info.setTime(groupTime);
            info.setBak1(groupBak1);
            info.setBak2(groupBak2);
            info.setIsOver(groupStatus);
        }
        cursor.close();
        db.close();
        return info;
    }

    public boolean isExist(String groupId) {
        if (TextUtils.isEmpty(groupId)) {
            return true;
        }
        boolean isResult = false;
        String sql = "select * from " + DBcolumns.GROUP_TABLE + " where " + DBcolumns.GROUP_NUM + "=?" + " and " + DBcolumns.GROUP_BAK1 + "=?";
        SQLiteDatabase db = helper.getWritableDatabase();
        Cursor cursor = db.rawQuery(sql, new String[]{groupId, PreferencesUtils.getInstance().getSharePreInt(PreferencesUtils.USER_ID) + ""});
        if (cursor == null) {
            isResult = false;
            return isResult;
        }
        if (cursor.moveToNext()) {
            isResult = true;
        } else {
            isResult = false;
        }
        cursor.close();
        db.close();
        return isResult;
    }

    public boolean isExistMsg(String msgId) {
        if (TextUtils.isEmpty(msgId)) {
            return true;
        }
        boolean isResult = false;
        String sql = "select * from " + DBcolumns.TABLE_MSG + " where " + DBcolumns.MSG_ID_S + "=?";
        SQLiteDatabase db = helper.getWritableDatabase();
        Cursor cursor = db.rawQuery(sql, new String[]{msgId});
        if (cursor == null) {
            isResult = false;
            return isResult;
        }
        if (cursor.moveToNext()) {
            isResult = true;
        } else {
            isResult = false;
        }
        cursor.close();
        db.close();
        return isResult;
    }
}
